/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.ce.daoimplementation;

import com.bionic.ce.beans.Bankacc;
import com.bionic.ce.beans.Currency;
import com.bionic.ce.beans.User;
import com.bionic.ce.dao.IBankaccDAO;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLBankaccDAO extends MYSQLDAO implements IBankaccDAO {

    @Override
    public void add(Bankacc bankacc) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO bankacc "
                    + "(`AccNum`, `Currency`, `BankName`, `SWIFT`, `CorrespondentBank`, User_id) "
                    + "VALUES(?,?,?,?,?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, bankacc.getAccNum());

            String shortname = bankacc.getCurrency();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            boolean b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(2, bankacc.getCurrency());
            } else {
                throw new SQLException("Such currency with shortname=" + bankacc.getCurrency() + " doesnt exists");
            }

            ptmt.setString(3, bankacc.getBankName());
            ptmt.setString(4, bankacc.getSwift());
            ptmt.setString(5, bankacc.getCorrespondentBank());

            int id = bankacc.getUserId();
            User user = new User();
            user.setUserid(id);
            b = new MySQLUserDAO().findByDynamicSelect(user).isEmpty();
            if (!b) {
                ptmt.setInt(6, bankacc.getUserId());
            } else {
                throw new SQLException("Such User with id=" + bankacc.getUserId() + " doesnt exists");
            }

            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(Bankacc bankacc) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE bankacc SET `AccNum`=?, `Currency`=?, `BankName`=?, `SWIFT`=?, `CorrespondentBank`=?"
                    + "WHERE `id`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, bankacc.getAccNum());

            String shortname = bankacc.getCurrency();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            boolean b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(2, bankacc.getCurrency());
            } else {
                throw new SQLException("Such currency with shortname=" + bankacc.getCurrency() + " doesnt exists");
            }

            ptmt.setString(3, bankacc.getBankName());
            ptmt.setString(4, bankacc.getSwift());
            ptmt.setString(5, bankacc.getCorrespondentBank());
            ptmt.setInt(6, bankacc.getId());

            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteById(int id) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM bankacc WHERE `id`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, id);
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Bankacc> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<Bankacc> result = new ArrayList();
            String queryString = "SELECT * FROM bankacc";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Bankacc bankacc = new Bankacc();
                bankacc.setId(resultSet.getInt("id"));
                bankacc.setAccNum(resultSet.getInt("AccNum"));
                bankacc.setCurrency(resultSet.getString("Currency"));
                bankacc.setBankName(resultSet.getString("BankName"));
                bankacc.setSwift(resultSet.getString("SWIFT"));
                bankacc.setCorrespondentBank(resultSet.getString("CorrespondentBank"));
                bankacc.setUserId(resultSet.getInt("User_id"));
                result.add(bankacc);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Bankacc> findByDynamicSelect(Bankacc bankacc) throws SQLException {
        connection = getConnection();
        try {
            List<Bankacc> result = new ArrayList();
            String queryString = "SELECT * FROM bankacc WHERE ";
            int i = 0;
            if (bankacc.getId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`id`=" + bankacc.getId();
                i++;
            }
            if (bankacc.getAccNum() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`AccNum`=" + bankacc.getAccNum();
                i++;
            }
            if (bankacc.getCurrency() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Currency`=\"" + bankacc.getCurrency() + "\"";
                i++;
            }
            if (bankacc.getBankName() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`BankName`=\"" + bankacc.getBankName() + "\"";
                i++;
            }
            if (bankacc.getSwift() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`SWIFT`=\"" + bankacc.getSwift() + "\"";
                i++;
            }
            if (bankacc.getCorrespondentBank() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`CorrespondentBank`=\"" + bankacc.getCorrespondentBank() + "\"";
                i++;
            }
            if (bankacc.getUserId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`User_id`=" + bankacc.getUserId();
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                bankacc = new Bankacc();
                bankacc.setId(resultSet.getInt("id"));
                bankacc.setAccNum(resultSet.getInt("AccNum"));
                bankacc.setCurrency(resultSet.getString("Currency"));
                bankacc.setBankName(resultSet.getString("BankName"));
                bankacc.setSwift(resultSet.getString("SWIFT"));
                bankacc.setCorrespondentBank(resultSet.getString("CorrespondentBank"));
                bankacc.setUserId(resultSet.getInt("User_id"));
                result.add(bankacc);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }
}
